package com.bbs.toutiao.mapper.admin;

import com.bbs.toutiao.vo.Admin.AdminRegisterCountVo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * 描述: 后台管理主页mapper
 * 作者： zhangBin
 * 时间： 2024/3/5 20:17
 * 版本： 1.0
 */

@Mapper
public interface AdminHomeMapper {

    //查询每日注册人数
    @Select("SELECT \n" +
            "    DATE(created_at) as register_date, \n" +
            "    COUNT(*) as register_count\n" +
            "FROM \n" +
            "    t_users\n" +
            "GROUP BY \n" +
            "    register_date\n" +
            "ORDER BY \n" +
            "    register_date")
    List<AdminRegisterCountVo> selectRegisterCount();

    //查询每个分类的文章数量
    @Select("SELECT c.class_name AS categoryName, COUNT(a.id) AS articleCount\n" +
            "FROM t_classify c\n" +
            "LEFT JOIN t_articles a ON c.id = a.class_id\n" +
            "GROUP BY c.class_name;")
    List<Map<String,Long>> selectArticleClassCount();

    //统计每日的评论数量,一共统计20条每日评论
    @Select("SELECT comment_date, COUNT(*) AS comment_count\n" +
            "FROM (\n" +
            "    SELECT DATE(created_at) AS comment_date\n" +
            "    FROM t_comments\n" +
            "    ORDER BY created_at DESC\n" +
            ") AS subquery\n" +
            "GROUP BY comment_date\n" +
            "ORDER BY comment_date DESC limit\n" +
            "20;")
    List<Map<String, String>> selectCommentCount();
}
